package Database;
import java.sql.*;
import java.util.ArrayList;

import Model.Customer;



public class DBCustomer implements IFDBCustomer{ 
	 private  Connection con;
	    public DBCustomer() {
	        con = DbConnection.getInstance().getDBcon();
	    }
	    //Implements the methods from the interface
	    // get all employees
	    public  Customer findCustomer(int id, boolean retriveAssociation)
	    {   String wClause = "  id = '" + id + "'";
	        return singleWhere(wClause, retriveAssociation);
	    }
	    public  Customer searchCustomerName(String attValue, boolean retriveAssociation)
	    {
	        String wClause = "name like '%" + attValue + "%'";
	        System.out.println("SearchCustomer " + wClause);
	        return singleWhere(wClause, retriveAssociation);
	    }
	    
	    private  Customer singleWhere(String wClause, boolean retrieveAssociation)
		{
			ResultSet results;
			Customer customer = new Customer();
	                
		        String query =  buildQuery(wClause);
	                System.out.println(query);
			try{ // read the Customer from the database
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 		results = stmt.executeQuery(query);
			}//end try	
		 	catch(Exception e){
		 		System.out.println("Query exception: "+e);
		 	}
			return customer;
		}




private String buildQuery(String wClause)
{
        String query="SELECT name, address, city FROM customer"; 
        
       

	if (wClause.length()>0)
		query = query + " where " + wClause;
		
	return query;
}


public  int insertCustomer(Customer cus) throws Exception
{  //call to get the next ssn number

   int rc = -1;
   String query="INSERT INTO employee(name, address, city, phone, type, id)  VALUES('"+
  		cus.getName()  + "','"  +
	    cus.getAddress()  + "','"  +
        cus.getCity() + "','" +
	    cus.getPhone() + "','" +
        cus.getType()  + "','" +
	    cus.getId() + "','" ;
                 

   System.out.println("insert : " + query);
  try{ // insert new employee +  dependent
      Statement stmt = con.createStatement();
      stmt.setQueryTimeout(5);
 	  rc = stmt.executeUpdate(query);
      stmt.close();
  }//end try
   catch(SQLException ex){
      System.out.println("Customer ikke oprettet");
      throw new Exception ("Customer is not inserted correct");
   }
   return(rc);
}
private Customer buildCustomer(ResultSet results)
{
   Customer customer = new Customer();
   
     try{
           customer.setName(results.getString("name"));
           customer.setAddress(results.getString("address"));
           customer.setCity(results.getString("city"));
           customer.setPhone(results.getLong("phone"));
           customer.setType(results.getString("type"));
           
      }
     catch(Exception e)
     {
         System.out.println("building customer object");
     }
    
    return customer;
    
}

public int deleteCustomer(int id)
{
   int rc = -1;
   PreparedStatement pstmt = null;
   String delete = "delete from customer where id = ?";
   System.out.println(delete);
     try{
        pstmt = con.prepareStatement(delete);
        pstmt.setInt(1,id);
        rc = pstmt.executeUpdate();                       
     }
     catch(SQLException sqlE)
     {
         System.out.println("SQL Error");
         System.out.println(sqlE.getMessage());   
     }
     catch(Exception e){
         e.getMessage();
         
     }
    return rc;
}
public int updateCustomer(Customer cus)
{
	Customer customer  = cus;
	int rc=-1;

	String query="UPDATE customer SET "+
	 	  "name ='"+ customer.getName()+"', "+
	 	  "address ='"+ customer.getAddress() + "', " +
                      "city ='"+ customer.getCity() + "', " +
                      "id ='"+ customer.getId() + "' " +
	          " WHERE id = '"+ customer.getId() + "'";
            System.out.println("Update query:" + query);
		try{ // update employee
 		Statement stmt = con.createStatement();
 		stmt.setQueryTimeout(5);
 	 	rc = stmt.executeUpdate(query);

 	 	stmt.close();
	}//slut try
 	catch(Exception ex){
 	 	System.out.println("Update exception in customer db: "+ex);
  	}
	return(rc);
}
@Override
public Customer searchCustomerSsn(String id, boolean b) {
	// TODO Auto-generated method stub
	return null;
}

}
